Fixed Server roles should have only authorized users or groups assigned as members.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-2427	DM0530-SQLServer9	SV-23758r2_rule	ECLP-1	Medium

Description
Fixed server roles provide a mechanism to grant groups of privileges to users. These privilege groupings are defined by the installation or upgrade of the SQL Server software at the discretion of Microsoft. Memberships in these roles granted to users should be strictly controlled and monitored. Privileges assigned to these roles should be reviewed for change after software upgrade or maintenance to ensure that the privileges continue to be appropriate to the assigned members.

Description

Fixed server roles provide a mechanism to grant groups of privileges to users. These privilege groupings are defined by the installation or upgrade of the SQL Server software at the discretion of Microsoft. Memberships in these roles granted to users should be strictly controlled and monitored. Privileges assigned to these roles should be reviewed for change after software upgrade or maintenance to ensure that the privileges continue to be appropriate to the assigned members.

STIG	Date
Microsoft SQL Server 2005 Instance Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-20128r2_chk )
From the query prompt: EXEC SP_HELPSRVROLEMEMBER 'bulkadmin' EXEC SP_HELPSRVROLEMEMBER 'dbcreator' EXEC SP_HELPSRVROLEMEMBER 'diskadmin' EXEC SP_HELPSRVROLEMEMBER 'processadmin' EXEC SP_HELPSRVROLEMEMBER 'securityadmin' EXEC SP_HELPSRVROLEMEMBER 'serveradmin' EXEC SP_HELPSRVROLEMEMBER 'setupadmin' EXEC SP_HELPSRVROLEMEMBER 'sysadmin' Verify authorization of each member listed in the System Security Plan. If any members are not authorized, this is a Finding.

Check Text ( C-20128r2_chk )

From the query prompt:

EXEC SP_HELPSRVROLEMEMBER 'bulkadmin'
EXEC SP_HELPSRVROLEMEMBER 'dbcreator'
EXEC SP_HELPSRVROLEMEMBER 'diskadmin'
EXEC SP_HELPSRVROLEMEMBER 'processadmin'
EXEC SP_HELPSRVROLEMEMBER 'securityadmin'
EXEC SP_HELPSRVROLEMEMBER 'serveradmin'
EXEC SP_HELPSRVROLEMEMBER 'setupadmin'
EXEC SP_HELPSRVROLEMEMBER 'sysadmin'

Verify authorization of each member listed in the System Security Plan. If any members are not authorized, this is a Finding.

Fix Text (F-14652r1_fix)
Remove fixed server role assignments from unauthorized users. Grant fixed roles to authorized personnel only. Remove unauthorized accounts from assigned roles. From the query prompt: EXEC SP_DROPSRVROLEMEMBER '[account name]', '[fixed server role name]' Replace [account name] with the name of the account and [fixed server role name] with the name of the fixed server role.